package sui.utils.excel.test;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.alibaba.fastjson.JSON;

import sui.utils.date.DateTimeUtils;
import sui.utils.excel.ReaderExcelUtils;

/**
 * <p>
 * Description: 读取Excel生成SQL(MySQL)
 * </p>
 * @author XQF SUI
 * @created 2017年4月21日 上午10:22:00
 * @version 1.0
 */
public class ReaderExcelGenerateSQLReco {

	private static final String REGEX = "\\$\\{(.+?)\\}";
	
	private static final String BASE_SQL = "UPDATE t_customer_sensitive s SET s.crm = (SELECT com_emp_id FROM t_customer_smp_company_group WHERE com_emp_no = ${com_emp_no}), operate_time = NOW() WHERE customer_code = ${code};";

	/**
	 * 生成SQL文件路径
	 */
	public static final String SQL_FILE_PATH = "F:\\window\\Desktop\\";
	
	/**
	 * Excel文件路径
	 */
	private static final String EXCEL_FILE_PATH = "F:\\window\\Desktop\\其他渠道.xlsx";
	
	/**
	 * Excel sheet页坐标
	 */
	private static final Integer EXCEL_SHEET_INDEX = 0;
	
	/**
	 * 标题行坐标
	 */
	private static final Integer TITLE_ROW_INDEX = 0;
	
	/**
	 * 数据开始行坐标
	 */
	private static final Integer DATA_ROW_INDEX = 1;
	
	public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException {
		List<String> sqlList = readerExcel();
		
		StringBuilder builder = new StringBuilder();
		
		for (String sql : sqlList) {
			builder.append(sql).append(" \r\n");
			System.out.println(sql);
		}
		
		wirteToFile(builder.toString());
		
//		read();
	}
	
	public static void read() throws EncryptedDocumentException, InvalidFormatException, IOException {
		Set<String> set = new HashSet<String>();
		
		for (Row row : WorkbookFactory.create(new File(EXCEL_FILE_PATH)).getSheetAt(EXCEL_SHEET_INDEX)) {
			String val = ReaderExcelUtils.getStringCellValue(row.getCell(11));
			if (StringUtils.isNotBlank(val)) {
				set.add(val);
			}
		}
		
		System.out.println(JSON.toJSONString(set));
	}
	
	/**
	 * <p>
	 * Desc: 读取Excel
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:16
	 * @return
	 * @throws EncryptedDocumentException
	 * @throws InvalidFormatException
	 * @throws IOException
	 */
	private static List<String> readerExcel() throws EncryptedDocumentException, InvalidFormatException, IOException {
		
		Sheet sheet = WorkbookFactory.create(new File(EXCEL_FILE_PATH)).getSheetAt(EXCEL_SHEET_INDEX);
		
		Map<String, Integer> titleMap = readerTitle(sheet, TITLE_ROW_INDEX, new HashMap<String, Integer>()); 
		
		List<String> sqlList = new ArrayList<String>();
		
		if (titleMap == null || titleMap.isEmpty()) {
			return sqlList;
		}
		
		int i = 0;
		Set<Entry<String, Integer>> set = titleMap.entrySet();
		
		for (Row row : sheet) {
			if (i++ >= DATA_ROW_INDEX) {
				Map<String, String> paramMap = new HashMap<String, String>();
				
				for (Entry<String, Integer> entry : set) {
					paramMap = getCellValueToSQLValue(paramMap, entry.getKey(), row.getCell(entry.getValue()));
				}
				
				sqlList.add(replaceAll(new String(BASE_SQL), paramMap));
			}
		}
		
		return sqlList;
	}

	/**
	 * <p>
	 * Desc: 获取Cell值并转换为SQL数据
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:32
	 * @param paramMap
	 * @param key
	 * @param cell
	 * @return
	 */
	public static Map<String, String> getCellValueToSQLValue(Map<String, String> paramMap, String key, Cell cell) {
		
		String val = "";
		
		if (cell == null) {
			paramMap.put(key, val);
			return paramMap;
		}
		
		switch (cell.getCellTypeEnum()) {
		case STRING:
			if ("null".equals(dataFilter(cell.getStringCellValue()))) {
				val = dataFilter(cell.getStringCellValue());
			} else {
				val = '\'' + dataFilter(cell.getStringCellValue()) + '\'';
			}
			
			break;
		case NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				val = '\'' + DateTimeUtils.getTimeNormalString(cell.getDateCellValue()) + '\'';
			} else {
				val = String.valueOf(cell.getNumericCellValue());
				
				DecimalFormat df = new DecimalFormat("0");  
				  
				val = df.format(cell.getNumericCellValue());
				
				// ================== 定制化 ==================
				if ("-10".equals(val)) {
					val = "null";
				}
				
//				if (Pattern.compile("^[0-9]+.[0]+$").matcher(val).matches()) {
//					val  = val.substring(0, val.lastIndexOf("."));
//				}
				// ================== 定制化 ==================
				
				if (val.length() > 11) {
					val  = '\'' + val + '\'';
				}
			}
			
			break;
		case BOOLEAN:
			val = String.valueOf(cell.getBooleanCellValue());
			break;
		case FORMULA:
			// TODO 暂不处理
			break;
		case BLANK:
			break;
		default:
			break;
		}
		
		paramMap.put(key, val);
		
		return paramMap;
	}
	
	public static String dataFilter(String data) {
		return data.trim();
	}
	
	/**
	 * <p>
	 * Desc: 根据SQL模板生成SQL
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:05:59
	 * @param template
	 * @param data
	 * @return
	 */
	public static String replaceAll(String template, Map<String, String> data) {
		Pattern pattern = Pattern.compile(REGEX);
		Matcher matcher = pattern.matcher(template);
		StringBuffer sb = new StringBuffer();
		while (matcher.find()) {
			String key = matcher.group(1);
			String value = data.get(key);
			if (StringUtils.isBlank(value)) {
				value = "";
				matcher.appendReplacement(sb, value);
				break;
			}
			value = value.replaceAll("\\$", "\\\\\\$");
			matcher.appendReplacement(sb, value);
		}
		matcher.appendTail(sb);
		return sb.toString();
	}

	/**
	 * <p>
	 * Desc: 读取标题(KEY)
	 * <p>
	 * @author XQF SUI
	 * @param titleRowIndex 
	 * @param sheet 
	 * @param map 
	 * @return 
	 * @created 2017年4月21日 上午10:41:13
	 */
	private static Map<String, Integer> readerTitle(Sheet sheet, Integer titleRowIndex, Map<String, Integer> map) {
		
		if (map == null) {
			map = new HashMap<String, Integer>();
		}
		
		int i = 0;
		
		for (Cell cell : sheet.getRow(titleRowIndex)) {
			map.put(getStringCellValue(cell), i++);
		}
		
		return map;
	}
	
	/**
	 * <p>
	 * Desc: 获取String类型的Cell值
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:06:38
	 * @param cell
	 * @return
	 */
	private static String getStringCellValue(Cell cell) {
		String strCell = null;
		
		switch (cell.getCellTypeEnum()) {
		case STRING:
			strCell = cell.getStringCellValue();
			break;
		case NUMERIC:
			strCell = String.valueOf(cell.getNumericCellValue());
			break;
		case BOOLEAN:
			strCell = String.valueOf(cell.getBooleanCellValue());
			break;
		case BLANK:
			break;
		default:
			break;
		}
		
		return strCell;
	}
	
	/**
	 * <p>
	 * Desc: 写SQL文件
	 * <p>
	 * @author XQF SUI
	 * @created 2017年4月21日 下午2:07:01
	 * @param data
	 * @throws IOException
	 */
	private static void wirteToFile(String data) throws IOException {
		
		String filePath = SQL_FILE_PATH + "generate_sql_" + DateTimeUtils.getNowTimeShortString() + ".sql";
		
		File sqlFile = new File(filePath);
		
		if (!sqlFile.exists()) {
			sqlFile.createNewFile();
		}
		
		FileWriter fileWritter = new FileWriter(sqlFile, true);
		PrintWriter printWriter = new PrintWriter(fileWritter);
		printWriter.println(data);
		fileWritter.flush();
		printWriter.close();
		fileWritter.close();
        
        System.out.println("写入文件[ " + filePath + " ]完成!");
	}
}
